drop database cdcache_innodb_mit_PK_mit_FK;
create database cdcache_innodb_mit_PK_mit_FK;
use cdcache_innodb_mit_PK_mit_FK;

/*==============================================================*/
/* Datenbank fr Engine InnoDb mit Primr und Fremdschlsseln   */
/*==============================================================*/

/*==============================================================*/
/* Table: artists                                               */
/*==============================================================*/
create table artists
(
   artistId             int not null auto_increment,
   name                 varchar(255) not null,
   primary key (artistId)
) 
ENGINE=InnoDB;

/*==============================================================*/
/* Table: cds                                                   */
/*==============================================================*/
create table cds
(
   cddbId               bigint not null,
   artistId             int not null,
   title                varchar(255) not null,
   version              int not null,
   numTracks            int not null,
   extd                 text,
   disclen              int not null,
   year                 char(4),
   genreId              int not null,
   primary key (cddbId)
)
ENGINE=InnoDB;

/*==============================================================*/
/* Table: genres                                                */
/*==============================================================*/
create table genres
(
   genreId              int not null,
   genre                varchar(255),
   primary key (genreId)
)
ENGINE=InnoDB;

/*==============================================================*/
/* Table: tracks                                                */
/*==============================================================*/
create table tracks
(
   cddbId               bigint not null,
   trackNo              int not null,
   offset               int not null,
   title                varchar(255) not null,
   artistId             int,
   extt                 text,
   primary key (cddbId, trackNo)
)
ENGINE=InnoDB;

alter table cds add constraint FK_cds_artists foreign key (artistId)
      references artists (artistId) on delete restrict on update restrict;

alter table tracks add constraint FK_cddbIds_tracks foreign key (cddbID)
      references cds(cddbID);

alter table tracks add constraint FK_artistId_tracks foreign key (artistId)
      references artists(artistId);

alter table cds add constraint FK_cds_genres foreign key (genreId)
      references genres(genreId);
